<?php
/**
 * Object Relational Persistence Mapping Class for table: service
 *
 * @package	com.clearhealth
 * @author	ClearHealth Inc.
 */
class Service extends ORDataObject {

	/**#@+
	 * Fields of table: service mapped to class members
	 */
	var $service_id								= '';
	var $service_group_id						= '';
	var $service_unit_id						= '';
	var $code									= '';
	var $vi_name								= '';
	var $en_name								= '';
	var $disabled   							= '';
	var $price									= '';
	var $item_type = '';
	var $pay_insurance_support_id               =  '';
	/**#@-*/

	const BEFORE 	= "before";
	const AFTER		= "after";

	const SERVICE_TYPE 		= 	"service";
	const TEST_TYPE 		= 	"test";
	const COMPOSITE_TYPE	=	'composite';
	const RADIOLOGY_TYPE    =   'radiology';

	const REFRACTION = 1001041;
	const EYE_EXAM = 1000960;
	const PRE_OPERATION_SERVICE_ID = 1000540;

	const STATUS_OPEN		=	'open';
	const STATUS_INPROGRESS	=	'in_progress';
	const STATUS_WAITING	=	'waiting';
	const STATUS_CLOSED		=	'closed';
	/**
	 * DB Table
	 */
	var $_table = 'service';

	/**
	 * Primary Key
	 */
	var $_key = 'service_id';

	/**
	 * Internal Name
	 */
	var $_internalName = 'Service';

	/**
	 * Handle instantiation
	 */
	function Service() {
		parent::ORDataObject();
	}

	function get_price() {
		return $this->price;
	}

	public static function getServiceAutoCompleteByCode($serviceCode) {
	    $field    =   L10nHelper::changeField(L10nHelper::getLocale(), "name");
		$ds       =   new Datasource_sql();
		$db       =   Celini::dbInstance();
		$sql = array (
			'cols'	=> "service_id, code, $field name, price",
			'from'		=> "service",
			'where'		=> "code LIKE '%$serviceCode%'"
			);
		$ds->setup($db, $sql);
		return $ds->toArray();
	}
	
	public function getServiceAutoCompleteByName($serviceName) {
	    $field		=	L10nHelper::getLocale();
	    $field		=	L10nHelper::changeField($field, "name");
		$ds = new Datasource_sql();
		$sql = array (
			'cols'	=> "service_id, code, $field name, price",
			'from'		=> "$this->_table",
			'where'		=> "$field LIKE '%$serviceName%'"
			);
		$ds->setup($this->_db, $sql);
		return $ds->toArray();
	}
	
	
	public function fromDepartment($deptId) {
	    $field =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');
		$ds = new Datasource_sql();
		
		$sql = array (
			'cols'	=> "service_id, CONCAT_WS(' - ', $field, REPLACE(FORMAT(price, 0), ',', ' ')) AS name",
			'from'		=> "$this->_table ",
			'where'		=> "item_type='service' AND disabled = 0",
			'orderby'	=> "name"
			);
		if (!empty($deptId)) {
			$sql['where'] .= " AND department_id = $deptId";
		}
		$ds->setup($this->_db, $sql);
		return $ds->toArray("service_id", "name");
	}

	public function getServiceInfo($serviceGroupId, $disabled) {
		$field =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');
		$ds = new Datasource_sql();
		$sql = array (
			'cols'	=> "service_id, code, $field name,
				        price, insurance_price, disabled",
			'from'		=> "$this->_table",
			'orderby'	=> "disabled, code"
			);
		if ($serviceGroupId !== false) {
			$sql['where'] = "service_group_id = $serviceGroupId";
		} else {
			$sql['where'] = "service_group_id = 0";
		}
		

		if ($disabled !== false) {
			$sql['where'] .= " AND disabled = $disabled";
		}
		$ds->setup($this->_db, $sql);
		return $ds->toArray();
	}

	public function getServices($groupId) {
		$field =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');
		$ds = new Datasource_sql();
		$sql = array (
			'cols'		=> "service_id, $field name",
			'from'		=> "$this->_table",
			'where'		=> "service_group_id = $groupId",
			'orderby' 	=> "disabled",
		);

		$ds->setup($this->_db, $sql);
		return $ds->toArray();

	}

	public function getServicesById($Id) {
		$field =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');
		$ds = new Datasource_sql();
		$sql = array (
			'cols'		=> "$field name",
			'from'		=> "$this->_table",
			'where'		=> "service_id = $Id",
			'orderby' 	=> "disabled",
		);

		$ds->setup($this->_db, $sql);
		$result = $ds->toArray();
		if (empty($result)) return '';
		return $result[0]['name'];
	}

	// TODO ptnguyen 2010-03-17
	// service schema has been changed
	// required_insurance_price... dont use any more
	public function getNotSurgeryServiceInfo($deptId, $disabled) {
		$field =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');

		$ds = new Datasource_sql();
		$sql = array (
			'cols'	=> "service_id, t.$field name,
				price, required_insurance_price, volunteered_insurance_price,
				patient_required_insurance_price, patient_volunteered_insurance_price,
				payment_type, disabled",
			'from'		=> "$this->_table t",
			'where'		=> "item_type='service' AND
			                 service_id NOT IN (SELECT service_id
			                                     FROM surgery_service)",
			'orderby'	=> "disabled"
			);
		if ($deptId !== false) {
			$sql['where'] .= " AND department_id = $deptId";
		}

		if ($disabled !== false) {
			$sql['where'] .= " AND disabled = $disabled";
		}
		$ds->setup($this->_db, $sql);
		return $ds->toArray();
	}

	public function getNoCoefficientServiceInfo($employeeId, $groupId, $disabled) {
	    $field =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');
		
		$ds = new Datasource_sql();
		$sql = array (
			'cols'	=> "s.service_id, s.$field name,
		              		s.price, s.disabled",
			'from'		=> "$this->_table s
			                 LEFT JOIN service_group sg ON s.service_group_id=sg.service_group_id",
			'where'		=> "s.item_type='service' AND s.service_id NOT IN (SELECT service_id
				FROM fee_coefficient WHERE person_id=$employeeId)",
			'orderby'	=> "disabled"
			);
		if ($groupId !== false) {
			$sql['where'] .= " AND sg.service_group_id = $groupId";
		}

		if ($disabled !== false) {
			$sql['where'] .= " AND disabled = $disabled";
		}
		$ds->setup($this->_db, $sql);
		return $ds->toArray();
	}

	public function getTestItemInfo() {
	    $field =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');
	    		
		$ds = new Datasource_sql();
		$sql = array (
			'cols'	=> "service_id, t.$field name,
				price, required_insurance_price, volunteered_insurance_price,
				patient_required_insurance_price, patient_volunteered_insurance_price,
				payment_type, disabled",
			'from'		=> "$this->_table t",
			'where'		=> "item_type='test'",
			'orderby'	=> "disabled"
			);
		$ds->setup($this->_db, $sql);
		return $ds->toArray();
	}
	
	public function getMappedServices($groupId){
	    $field =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');
		$ds = new Datasource_sql();
		$sql = array(
            'cols'  => "service_id, $field name",
            'from'  => "$this->_table",
        	'where' => "service_group_id = $groupId",
            'orderby' => "name"
            );
        $ds->setup($this->_db, $sql);
        return $ds->toArray();

	}
	public function getUnmappedServices($groupId){
	    $field =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');
		$ds = new Datasource_sql();
		$sql = array(
            'cols'  	=> 	"service_id, t.$field  name",
            'from'  	=> 	"$this->_table t",
        	'where'	 	=> 	"service_group_id = 0",
            'orderby'	=> 	"name"
            );
        $ds->setup($this->_db, $sql);
        return $ds->toArray();
	}

	public function AddToServiceGroup($groupId, $SeviceIds){
		$ds = new Datasource_sql();
		$sql = "UPDATE $this->_table SET service_group_id = $groupId WHERE false ";
		foreach($SeviceIds as $serviceId){
			 $sql .= " OR service_id = $serviceId ";
		}
		$sql .= ";";
		$res = $this->_execute($sql);
		return ;
	}

	public function setRole(){
		$sql	=	"SELECT * FROM role_service
			WHERE $this->_key =$this->service_id LIMIT 1";
		$res	=	$this->_execute($sql);
		if($res && !$res->EOF){
			return true;
		}
		return false;
	}

	public function getAssignedServices(){
	    $field =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');
		
		$ds = new Datasource_sql();
		$sql = array(
					'cols' => "r.service_id, s.$field name ",
					'from' => "role_service r, service s",
					'where' => "r.service_id = s.service_id");
		$ds->setup($this->_db, $sql);
		return $ds->toArray();
	}

	public function getNotAssignedServices($groupId){
	    $field =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');
		
		$ds = new Datasource_sql();
		$sql = array(
					'cols' => "service_id, s.$field name",
					'from' => "service s",
					'where' => "service_id NOT IN (SELECT * FROM role_service) AND service_group_id = $groupId");
		$ds->setup($this->_db, $sql);
		return $ds->toArray();
	}

	public function AddRoleToService($serviceIds){
		$sql = "INSERT INTO role_service(service_id) VALUES ";
		foreach($serviceIds as $id){
			$sql .= "($id),";
		}
		$sql = substr($sql,0,-1);
		return $this->_execute($sql);
	}

	public function RemoveRoleToService($serviceIds){
		$sql = "DELETE FROM role_service WHERE false  ";
		foreach($serviceIds as $id){
			$sql .= " OR service_id = $id";
		}
		$this->_execute($sql);
	}

	   // TODO ptnguyen 2010-03-17
    // service schema has been changed
    // required_insurance_price... dont use any more
    public function getServicesDetailById($Id) {
        $field =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');
        
        $ds = new Datasource_sql();
        $sql = array (
            'cols'      => "t.$field name, price, required_insurance_price, volunteered_insurance_price,
            patient_required_insurance_price, patient_volunteered_insurance_price",
            'from'      => "$this->_table t",
            'where'     => "service_id = $Id"
        );

        $ds->setup($this->_db, $sql);
        $result = $ds->toArray();
        if (empty($result)) return null;
        return $result[0];
    }


    public function getPrices($ticketId, $serviceId, $enumItemType) {
        // Insurance type of the ticket
        $prices = array('price'=>0, 'insurance_price'=>0, 'patient_price'=>0);
        $insMapObj = Celini::newOrdo("ServiceInsuranceMap");

        $info = $this->getServicesDetailById($serviceId);
        if ($info == null) {

            return $prices;

        }

        $itemType = Utils::getEnumKey(EnumerationConstant::SER_INS_ITEM_TYPE,
        $enumItemType);
        $isSupported = $insMapObj->isInsurranceSupported($serviceId, $itemType);

        $insType  =   Celini::newORDO('Ticket', $ticketId)->get('insurance');
        if ($insType == null || $insType == 'no' || $isSupported == false ) {
            $prices['price'] = $info['price'];
            $prices['insurance_price'] = 0;
            $prices['patient_price'] = $info['price'];
        } else if ($insType == 'volunteered') {
            $prices['price'] = $info['price'];
            $prices['insurance_price'] = $info['volunteered_insurance_price'];
            $prices['patient_price'] = $info['patient_volunteered_insurance_price'];
        } else if ($insType == 'required') {
            $prices['price'] = $info['price'];
            $prices['insurance_price'] = $info['required_insurance_price'];
            $prices['patient_price'] =  $info['patient_required_insurance_price'];
        }
        return $prices;
    }

    // TODO ptnguyen 2010-03-17
    // service schema has been changed
    // required_insurance_price... dont use any more
    public function getServicesByItemType($itemType, $disabled = false) {
        $field =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');
        
        $ds = new Datasource_sql();
        $sql = array (
            'cols'  => "service_id, t.$field name,
                price, required_insurance_price, volunteered_insurance_price,
                patient_required_insurance_price, patient_volunteered_insurance_price,
                payment_type, disabled",
            'from'      => "$this->_table t",
            'where'     => "item_type='$itemType'",
            'orderby'   => "l.$field, disabled"
            );
        if ($disabled !== false) {
            $sql['where'] .= " AND disabled = $disabled";
        }
        $ds->setup($this->_db, $sql);

        return $ds->toArray();
    }
    /**
     *check duplicate Code and Name
     * @return 0.=> Ok. 1->duplicated code; 2->duplicated name
     */
    public function checkDuplicate($code, $name, $id){
    	$ds = new Datasource_sql();
    	$field =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');
    	$sqlCode = array (
            'cols'  => "service_id, $field name",
            'from'      => "$this->_table",
            'where'     => "code = '$code'"
        );
        
        $sqlName = array (
            'cols'  => "service_id, $field name",
            'from'      => "$this->_table",
            'where'     => "$field = '$name'"
        );
        
        if($id > 0){
        	$sqlCode['where'] .= " AND service_id != $id";
        	$sqlName['where'] .= " AND service_id != $id";
        }
        
        $ds->setup($this->_db, $sqlCode);
        if(count($ds->toArray()) != 0){
        	return 1;
        }
        $ds->setup($this->_db, $sqlName);
   		if(count($ds->toArray()) != 0){
        	return 2;
        }
        return 0;
    }
}
?>
